import json
import frappe
from frappe import _, msgprint
from frappe.utils import flt, ceil,floor
from erpnext.manufacturing.doctype.production_plan import production_plan
from erpnext.manufacturing.doctype.production_plan.production_plan import (
    get_exploded_items,get_warehouse_list,get_materials_from_other_locations)
from six import iteritems
from pypika.terms import ExistsCriterion
from frappe.query_builder import Case, Criterion
from frappe.query_builder.functions import IfNull, Sum
from zelin_pp.utils import (
    get_item_code_map,get_projected_qty_map,get_qty, get_whole_number_uom_list,
    get_item_warehouse_qty_map, get_bin_qty_map,reverse_reserved_qty)


original_get_items_for_material_requests = production_plan.get_items_for_material_requests

@frappe.whitelist()
def custom_get_items_for_material_requests(doc, warehouses=None, get_parent_warehouse_data=None):
    """
    标准功能通过装配件(成品)最底层物料清单直接计算原材料需求,未考虑子装配件基于现有库存调减计划数量对原材料的影响，会导致原材料计划量大于实际需求量
    重构逻辑
    1. 基于成品及半成品计划数量(已考虑现有库存且手工调整过)展单层物料清单计算原材料需求，
    2. 按物料号合并计划数量，记录其需求来源明细到备注字段中
    3. 获取原材料可用库存，逐个分派库存
    4. 按最小订单、最小包装量及圆整数量进行调整
    5. 受托加工物料不考虑预期可用库存，取物料清单需求数量    
    """
    if isinstance(doc, str):
        doc = frappe._dict(json.loads(doc))

    if warehouses:
        warehouses = list(set(get_warehouse_list(warehouses)))

        if (
            doc.get("for_warehouse")
            and not get_parent_warehouse_data
            and doc.get("for_warehouse") in warehouses
        ):
            warehouses.remove(doc.get("for_warehouse"))

    doc["mr_items"] = []
    po_items = doc.get("po_items") if doc.get("po_items") else doc.get("items")
    #将子装配件(有物料清单，物料需求类型为采购)添加到成品清单中，一并转成原材料需求
    if doc.get("sub_assembly_items"):
        for sa_row in doc.get('sub_assembly_items') or []:
            sa_row = frappe._dict(sa_row)
            if sa_row.type_of_manufacturing == "Material Request":
                po_items.append(
                    frappe._dict(
                        {
                            "item_code": sa_row.production_item,
                            "required_qty": sa_row.qty,
                            "include_exploded_items": 0,
                        }
                    )
                )

    # Check for empty table or empty rows
    if not po_items or not [row.get("item_code") for row in po_items if row.get("item_code")]:
        frappe.throw(
            _("Items to Manufacture are required to pull the Raw Materials associated with it."),
            title=_("Items Required"),
        )

    company = doc.get("company")
    ignore_existing_ordered_qty = doc.get("ignore_existing_ordered_qty")
    bom_qty_customer_provided_item = doc.get('bom_qty_customer_provided_item')
    add_other_unplanned_qty = doc.get("add_other_unplanned_qty")
    include_safety_stock = doc.get("include_safety_stock")
    include_subcontracted_items = doc.get("include_subcontracted_items")
    include_non_stock_items = doc.get("include_non_stock_items")

    #一次性获取两类物料清单对照表,单层及展到底层的
    bom_no_list = {d.get('bom_no') or d.get('bom') for d in po_items if d.get('bom_no') or d.get('bom') and d.get('include_exploded_items')}
    if bom_no_list:
        exploded_bom_map = get_bom_map(company, bom_no_list, bom_item_table='BOM Explosion Item') if bom_no_list else {}
    bom_no_list = {d.get('bom_no') or d.get('bom') for d in (po_items or []) + (doc.get('sub_assembly_items') or []) 
        if d.get('bom_no') or d.get('bom') and not d.get('include_exploded_items')}    
    bom_map = get_bom_map(company, bom_no_list) if bom_no_list else {}
    item_master_map= get_item_master_map(company, {d.get('item_code') for d in po_items if not (d.get('bom_no') or d.get('bom'))})
    sub_assembly_items = {}
    if doc.get("skip_available_sub_assembly_item"):
        for d in doc.get("sub_assembly_items", []):
            sub_assembly_items.setdefault((d.get("production_item"), d.get("bom_no")), d.get("qty"))
    item_details = {}
    warehouses_for_msg = []
    for data in po_items + (doc.get('sub_assembly_items') or []):#成品+子装配件(半成品)
        warehouse = doc.get("for_warehouse") or data.get("warehouse")      #todo 需优化为允许设置在物料主数据和模块设置中，支持组类型仓库        
        if warehouse: warehouses_for_msg.append(warehouse)
        bom_no = data.get("bom") or data.get("bom_no")
        item_code = data.get("item_code")
        planned_qty = data.get("qty") or data.get("planned_qty") or data.get("pending_qty") #来自销售订单创建原材料物料需求
        if not planned_qty:continue
        include_exploded_items = data.get("include_exploded_items")        
        source_type = 'FG' if data.get('doctype') == 'Production Plan Item' else 'SFG'
        requirement_ref = f"{source_type} {data.get('idx')}"            
        if bom_no and planned_qty:
            ref_bom_map = bom_map if not include_exploded_items else exploded_bom_map
            get_subitems(item_details, bom_no, ref_bom_map, requirement_ref, include_non_stock_items, planned_qty)
        elif item_code:            
            item_dict = item_master_map.get(item_code,{})
            item_dict['qty'] = planned_qty
            existing_row = item_details.get(item_code)
            #合并多个需求源数量
            if existing_row:
                item_details[item_code].qty +=  item_dict['qty']
            else:
                item_details[item_code] = frappe._dict(item_dict)            
    #原标准功能按销售订单合并原材料数量因为生产计划中无销售订单属性，这段代码无实质意义    
    mr_items=[]
    bin_qty_map = get_bin_qty_map(company, {d.item_code for d in item_details.values()})
    reverse_reserved_qty(po_items, bin_qty_map)    
    whole_number_uom_list = get_whole_number_uom_list()
    for item_detail in item_details.values():
        if item_detail.qty and item_detail.qty > 0:
            mr_item = get_mr_item_dict(
                item_detail, bin_qty_map, whole_number_uom_list, include_safety_stock, ignore_existing_ordered_qty,
                bom_qty_customer_provided_item, add_other_unplanned_qty, warehouse)
            if mr_item:
                mr_items.append(mr_item)

    if (not ignore_existing_ordered_qty or get_parent_warehouse_data) and warehouses:
        new_mr_items = []
        for item in mr_items:
            get_materials_from_other_locations(item, warehouses, new_mr_items, company)

        mr_items = new_mr_items

    if not mr_items:
        to_enable = frappe.bold(_("Ignore Existing Projected Quantity"))
        warehouse = frappe.bold(','.join(warehouses_for_msg))
        message = (
            _(
                "As there are sufficient raw materials, Material Request is not required for Warehouse {0}."
            ).format(warehouse)
            + "<br><br>"
        )
        message += _("If you still want to proceed, please enable {0}.").format(to_enable)

        frappe.msgprint(message, title=_("Note"))

    return mr_items

def get_mr_item_dict(
    row, bin_qty_map, wn_uom_map, include_safety_stock, ignore_existing_ordered_qty,
    bom_qty_customer_provided_item, add_other_unplanned_qty, warehouse):
    """获取原材料明细行数据，同时处理数量逻辑：最小订单量，最小包装量，单位取整，安全库存，客供品"""
    item_code = row.get('item_code')
    warehouse = row.get("warehouse") or row.get("item_group_warehouse") or warehouse            
    bin_dict = bin_qty_map.get((item_code, warehouse), {})
    projected_qty = bin_dict.get("projected_qty", 0)
    descriptions = []
    default_material_request_type = row.get("default_material_request_type")
    total_qty = row["qty"]
    required_qty = total_qty
    #受托加工物料使用物料清单需求数量即可，
    if default_material_request_type != 'Customer Provided' or not bom_qty_customer_provided_item:
        safety_stock = row["safety_stock"]
        if include_safety_stock and safety_stock:
            required_qty += flt(safety_stock)
            descriptions.append(_('Added Safety Stock {0}').format(safety_stock))

        if projected_qty < 0:
            if add_other_unplanned_qty:
                required_qty += abs(projected_qty)
                descriptions.append(_('Added unplanned qty {0}').format(abs(projected_qty)))
        elif projected_qty and not ignore_existing_ordered_qty:
            if projected_qty < total_qty:
                required_qty = total_qty - projected_qty
                descriptions.append(_('Partial stock qty {0} used').format(projected_qty))
            else:            
                descriptions.append(_('Full stock qty {0} used').format(required_qty))
                required_qty = 0
    required_qty = required_qty / row.conversion_factor
    old_required_qty = required_qty
    required_qty = get_qty(required_qty, row.min_order_qty, row.min_pack_qty)
    if required_qty != old_required_qty:    
        descriptions.append(_('Qty changed from {0} to {1} per min order qty or min pack qty').format(flt(old_required_qty,2), required_qty))
    
    if not row["purchase_uom"]:
        row["purchase_uom"] = row["stock_uom"]
    if row["purchase_uom"] in wn_uom_map and floor(required_qty) != required_qty: # "must_be_whole_number"
        required_qty = ceil(required_qty)
        descriptions.append(_('Changed qty from {0} to {1} per UOM').format(required_qty, ceil(required_qty)))

    if required_qty > 0:        #todo 可以考虑在界面上加个勾选框允许用户选择在原材料明细中显示库存足够计划数量为0的记录，
        return {
            "item_code": row.item_code,
            "item_name": row.item_name,
            "quantity": required_qty,
            "conversion_factor": row.conversion_factor,
            "required_bom_qty": total_qty,
            "stock_uom": row.stock_uom,
            "warehouse": warehouse,
            "safety_stock": row.safety_stock,
            "actual_qty": bin_dict.get("actual_qty", 0),
            "projected_qty": bin_dict.get("projected_qty", 0),
            "ordered_qty": bin_dict.get("ordered_qty", 0),
            "reserved_qty_for_production": bin_dict.get("reserved_qty_for_production", 0),
            "min_order_qty": row.min_order_qty,
            "min_pack_qty": row.min_pack_qty,
            "material_request_type": row.default_material_request_type,
            #"sales_order": sales_order,
            "description": "\r\n".join([row.description]+ descriptions),
            "uom": row.purchase_uom,
        }

def get_subitems(item_details, bom_no, bom_map, requirement_ref, include_non_stock_items, planned_qty=1):
    """requirement_ref 需求来源于成品或半成品明细哪一行 """
    for d in bom_map.get(bom_no) or []:
        item_code = d.item_code        
        qty = d.qty * planned_qty          
        if item_code in item_details:
            item_details[item_code].qty += qty            
        else:
            #if not d.conversion_factor and d.purchase_uom:
            #    d.conversion_factor = get_uom_conversion_factor(d.item_code, d.purchase_uom)            
            item_details[item_code] = frappe._dict(d)   #避免数量字段修改影响原bom用量
            item_details[item_code].qty = qty
        #增加需求明细来源到描述字段
        requirement_detail = f'{requirement_ref},{bom_no},{planned_qty},{d.qty},{qty}'
        item_details[item_code].description = "\r\n".join([item_details[item_code].description, requirement_detail])


def get_bom_map(company, bom_no_list=[], bom_item_table='BOM Item'):
    """一次性获取物料清单
    {'BOM-半成品一阶-001': 
        [{'bom_no': 'BOM-半成品一阶-001', 'item_code': '底层原材料一', 'qty': 1.0}]
    }"""
    bom = frappe.qb.DocType('BOM')
    bom_item = frappe.qb.DocType(bom_item_table)
    item = frappe.qb.DocType('Item')
    item_default = frappe.qb.DocType("Item Default")
    item_uom = frappe.qb.DocType("UOM Conversion Detail")
    query = frappe.qb.from_(bom
        ).join(bom_item
        ).on(bom.name == bom_item.parent
        ).join(item
        ).on(bom_item.item_code == item.name
        ).left_join(item_default
        ).on((item.name == item_default.parent) & (item_default.company == company)
        ).left_join(item_uom
        ).on((item.name == item_uom.parent) & (item_uom.uom == item.purchase_uom)
        ).select(bom.name.as_('bom_no'),
            bom_item.item_code,
            bom_item.item_name,
            item.is_stock_item,
            item.is_sub_contracted_item,
            bom_item.description,
            bom_item.stock_uom,
            item.min_order_qty,
            item.min_pack_qty,
            item.safety_stock,
            item.default_material_request_type,
            item_default.default_warehouse.as_('warehouse'),
            Case().when(item.purchase_uom.isnull(), item.stock_uom
                ).else_(item.purchase_uom).as_('purchase_uom'),
            IfNull(item_uom.conversion_factor,1).as_('conversion_factor'),
            IfNull(Sum(bom_item.stock_qty/IfNull(bom.quantity,1)),0).as_('qty')
        ).where(
            Criterion.all([
                bom.company == company,
                bom.docstatus == 1,
                bom.name.isin(bom_no_list),
                item.is_sub_contracted_item == 0,                
                item.default_material_request_type.isin(['Purchase','Customer Provided'])
            ])
        ).groupby(bom.name, bom_item.item_code)
    if bom_item_table == 'BOM Item':
        query = query.where((bom_item.bom_no.isnull()) | (bom_item.bom_no == ''))
    data = query.run(as_dict=True)
    bom_map = {}
    for d in data:
        bom_childs = bom_map.setdefault(d.bom_no, [])
        bom_childs.append(d)
    return bom_map

def get_item_master_map(company, item_code_list=None):
    if not item_code_list: return {}
    item = frappe.qb.DocType('Item')
    item_default = frappe.qb.DocType("Item Default")
    item_uom = frappe.qb.DocType("UOM Conversion Detail")
    data = frappe.qb.from_(item
        ).left_join(item_default
        ).on((item.name == item_default.parent) & (item_default.company == company)
        ).left_join(item_uom
        ).on((item.name == item_uom.parent) & (item_uom.uom == item.purchase_uom)
        ).select(item.name.as_('item_code'),                        
            item.is_sub_contracted_item,
            item.item_name,
            item.description,
            item.stock_uom,
            item.min_order_qty,
            item.min_pack_qty,
            item.safety_stock,
            item.default_material_request_type,
            item_default.default_supplier,
            item_default.default_warehouse.as_('warehouse'),
            Case().when(item.purchase_uom.isnull(), item.stock_uom
                ).else_(item.purchase_uom).as_('purchase_uom'),             
            IfNull(item_uom.conversion_factor,1).as_('conversion_factor')
        ).where(
            Criterion.all([                
                item.name.isin(item_code_list),                
                item.default_material_request_type.isin(['Purchase','Customer Provided'])
            ])
        ).run(as_dict=True)    
    item_map = {d.item_code:d for d in data}
    return item_map

production_plan.get_items_for_material_requests = custom_get_items_for_material_requests

def custom_get_sales_orders(self):
    """除原work_order_qty之外，再考虑plan_reserved_stock_qty"""
    bom = frappe.qb.DocType("BOM")
    pi = frappe.qb.DocType("Packed Item")
    so = frappe.qb.DocType("Sales Order")
    so_item = frappe.qb.DocType("Sales Order Item")

    open_so_subquery1 = frappe.qb.from_(bom).select(bom.name).where(bom.is_active == 1)

    open_so_subquery2 = (
            frappe.qb.from_(pi)
            .select(pi.name)
            .where(
                    (pi.parent == so.name)
                    & (pi.parent_item == so_item.item_code)
                    & (
                        ExistsCriterion(
                                frappe.qb.from_(bom).select(bom.name).where((bom.item == pi.item_code) & (bom.is_active == 1))
                        )
                    )
            )
    )

    open_so_query = (
            frappe.qb.from_(so)
            .from_(so_item)
            .select(so.name, so.transaction_date, so.customer, so.base_grand_total)
            .distinct()
            .where(
                    (so_item.parent == so.name)
                    & (so.docstatus == 1)
                    & (so.status.notin(["Stopped", "Closed"]))
                    & (so.company == self.company)
                    & ((so_item.qty  * so_item.conversion_factor) > (so_item.work_order_qty + so_item.plan_reserved_stock_qty))
            )
    )

    date_field_mapper = {
            "from_date": self.from_date >= so.transaction_date,
            "to_date": self.to_date <= so.transaction_date,
            "from_delivery_date": self.from_delivery_date >= so_item.delivery_date,
            "to_delivery_date": self.to_delivery_date <= so_item.delivery_date,
    }

    for field, value in date_field_mapper.items():
            if self.get(field):
                    open_so_query = open_so_query.where(value)

    for field in ("customer", "project", "sales_order_status"):
            if self.get(field):
                    so_field = "status" if field == "sales_order_status" else field
                    open_so_query = open_so_query.where(so[so_field] == self.get(field))

    if self.item_code and frappe.db.exists("Item", self.item_code):
            open_so_query = open_so_query.where(so_item.item_code == self.item_code)
            open_so_subquery1 = open_so_subquery1.where(
                    self.get_bom_item_condition() or bom.item == so_item.item_code
            )

    open_so_query = open_so_query.where(
            (ExistsCriterion(open_so_subquery1) | ExistsCriterion(open_so_subquery2))
    )

    open_so = open_so_query.run(as_dict=True)

    return open_so

production_plan.get_sales_orders = custom_get_sales_orders